Field of Invention
The invention relates generally to database server systems, and more specifically to optimizing prepared SQL statement processing by reusing query plans across client connections.
Description of the Background Art
Computer database server systems have become a prevalent means for data storage and retrieval. A database will commonly access the underlying data using a database server system. A client issues a query to the database server system that conforms to a defined query language. This simplifies the client's interaction with the database by not requiring that the client know or understand the underlying structure of the physical database. Because of this, however, it is imperative that the database server system generate and execute the query in a manner which is most efficient for the underlying database.
The database server system achieves a significant performance improvement by reusing query plans that have been previously generated by the database server system. Building and compiling query plans are expensive processes because the database server system requires access to global memory caches to search for, retrieve and store query plans. Access to global memory caches is achieved by way of an access spinlock. Each time the database server system uses the access spinlock, the database server system serializes the processes that require access to the global memory cache in a parallel computing environment. Excessive use of access spinlocks affects the database server system's performance, decreases the database server system's efficiency, and wastes valuable CPU cycles.
Therefore, what is needed is a database server system that does not build and compile query plans that have been previously saved in the global memory cache. What is also needed is a database server system that is operable to reuse query plans generated by SQL queries that have been received from multiple clients.
When clients use a conventional database server system, the conventional database server system saves objects that identify query plans in a local memory cache. The local memory cache is reserved for a particular client. Because the objects that identify query plans are saved in the local memory cache, the conventional database server system is not operable to determine that an identical query plan has been generated for multiple clients. If the conventional database server system is unable to determine that the identical query plans have been generated, the conventional database server system is not operable to reuse the generated query plan for multiple clients. Therefore, what are needed are systems and methods that are operable to reuse the generated query plan for clients that request an execution of the same SQL statement. What are also needed are systems and methods that allow cloning of the generated query plan when the generated query plan is requested by multiple client connections.